package YunNotes.allnotes.dao;


import YunNotes.allnotes.model.Note;
import YunNotes.util.db.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author xxx
 */
public class NoteDao {

    /**
     * 增加笔记
     */

    public void addNote(Note n) throws SQLException {
        //获得数据库的连接
        Connection conn = DBUtil.getConn();
        //执行insert语句
        String sql = "" +
                "insert into allnotes" +
                "(id,user_name,num,headline,content,create_time,praise_points,page_view,praise_id)" +
                "values(" +
                "?,?,?,?,?,current_date(),0,0,'P,')";

        //执行sql语句方法
        PreparedStatement p = conn.prepareStatement(sql);

        //给sql语句传入参数
        p.setInt(1, n.getId());
        p.setString(2, n.getUserName());
        p.setInt(3, n.getNum());
        p.setString(4, n.getHeadline());
        p.setString(5, n.getContent());

        //调用execute时上述语句才应用
        p.execute();
    }

    /**
     * 设置分类
     */

    public void addLabel(String label, int num) throws SQLException {
        //获得数据库的连接
        Connection conn = DBUtil.getConn();

        String sql = " update allnotes " +
                " set label=?" +
                " where num=? ";
        //执行sql语句方法
        PreparedStatement p = conn.prepareStatement(sql);

        p.setString(1, label);
        p.setInt(2, num);

        //调用execute时上述语句才应用
        p.execute();
    }

    /**
     * 设置分类顺序
     */

    public void addLabelNum(int labelNum, String label, int id) throws SQLException {
        //获得数据库的连接
        Connection conn = DBUtil.getConn();

        String sql = " update allnotes " +
                " set label_num=?" +
                " where label=? and id =? ";
        //执行sql语句方法
        PreparedStatement p = conn.prepareStatement(sql);

        p.setInt(1, labelNum);
        p.setString(2, label);
        p.setInt(3, id);

        //调用execute时上述语句才应用
        p.execute();
    }

    /**
     * 修改笔记内容
     */

    public void updateContent(String content, int num) throws SQLException {
        //获得数据库的连接
        Connection conn = DBUtil.getConn();

        String sql = " update allnotes " +
                " set content=? " +
                " where num=? ";

        //执行sql语句方法
        PreparedStatement p = conn.prepareStatement(sql);

        p.setString(1, content);
        p.setInt(2, num);

        //调用execute时上述语句才应用
        p.execute();
    }

    /**
     * 修改笔记标题
     */

    public void updateHeadline(String headline, int num) throws SQLException {
        //获得数据库的连接
        Connection conn = DBUtil.getConn();

        String sql = " update allnotes " +
                " set headline=? " +
                " where num=? ";

        //执行sql语句方法
        PreparedStatement p = conn.prepareStatement(sql);

        p.setString(1, headline);
        p.setInt(2, num);

        //调用execute时上述语句才应用
        p.execute();
    }

    /**
     * 根据分类的名字，修改分类的顺序
     */

    public void updateLabelNum(int labelNum, String label, int id) throws SQLException {
        //获得数据库的连接
        Connection conn = DBUtil.getConn();

        String sql = "" +
                " update allnotes " +
                " set label_num=?" +
                " where label=? and id =? ";

        //执行sql语句方法
        PreparedStatement p = conn.prepareStatement(sql);
        p.setInt(1, labelNum);
        p.setString(2, label);
        p.setInt(3, id);

        //调用execute时上述语句才应用
        p.execute();
    }

    /**
     * 删除分类（实则是讲label改为默认分类，讲LabelNum改为1）
     */

    public void delLabel(String label, int id) throws SQLException {
        //获得数据库的连接
        Connection conn = DBUtil.getConn();

        String sql = "" +
                " update allnotes " +
                " set label = '默认分类',label_num =1" +
                " where label=? and id=? ";

        //执行sql语句方法
        PreparedStatement p = conn.prepareStatement(sql);

        p.setString(1, label);
        p.setInt(2, id);

        //调用execute时上述语句才应用
        p.execute();
    }

    /**
     * 删除笔记
     */

    public void delNote(int num) throws SQLException {
        //获得数据库的连接
        Connection conn = DBUtil.getConn();

        String sql = "" +
                " delete from allnotes " +
                " where num=? ";

        //执行sql语句方法
        PreparedStatement p = conn.prepareStatement(sql);

        p.setInt(1, num);

        //调用execute时上述语句才应用
        p.execute();
    }

    /**
     * 查询个人的所有笔记
     */

    public List<Note> query(int id) throws SQLException {

        //获得数据库的连接
        Connection conn = DBUtil.getConn();
        StringBuilder sb = new StringBuilder();

        //先找id
        sb.append(" select num,headline,content,create_time from allnotes ");
        sb.append(" where  id=? ");

        PreparedStatement p = conn.prepareStatement(sb.toString());
        p.setInt(1, id);

        List<Note> notes = new ArrayList<Note>();
        Note n = null;

        ResultSet rs = p.executeQuery();

        while (rs.next()) {
            n = new Note();
            n.setId(id);
            n.setNum(rs.getInt("num"));
            n.setHeadline(rs.getString("headline"));
            n.setContent(rs.getString("content"));
            n.setCreateTime(rs.getDate("create_time"));
            notes.add(n);
        }
        return notes;
    }

    /**
     * 关键字查询第一步
     */

    public List<Note> key1(int id, String key) throws SQLException {

        //获得数据库的连接

        Connection conn = DBUtil.getConn();

        StringBuilder sb = new StringBuilder();

        sb.append(" select num,headline,content,create_time from allnotes ");
        sb.append(" where headline like ? and id = ? ");

        PreparedStatement p = conn.prepareStatement(sb.toString());

        p.setString(1, "%" + key + "%");
        p.setInt(2, id);

        List<Note> result = new ArrayList<>();
        Note n = null;
        ResultSet rs = p.executeQuery();
        while (rs.next()) {
            n = new Note();
            n.setNum(rs.getInt("num"));
            n.setHeadline(rs.getString("headline"));
            n.setContent(rs.getString("content"));
            n.setCreateTime(rs.getDate("create_time"));
            result.add(n);
        }
        return result;
    }

    /**
     * 关键字查询
     */

    public List<Note> key(int id, String key) throws SQLException {

        //获得数据库的连接

        Connection conn = DBUtil.getConn();

        StringBuilder sb = new StringBuilder();

        sb.append(" select num,headline,content,create_time from allnotes ");
        sb.append(" where content like ? and id = ? ");

        PreparedStatement p = conn.prepareStatement(sb.toString());

        p.setString(1, "%" + key + "%");
        p.setInt(2, id);

        List<Note> result = new ArrayList<>();
        result = key1(id, key);
        Note n = null;
        ResultSet rs = p.executeQuery();
        while (rs.next()) {
            n = new Note();
            n.setNum(rs.getInt("num"));
            n.setHeadline(rs.getString("headline"));
            n.setContent(rs.getString("content"));
            n.setCreateTime(rs.getDate("create_time"));
            result.add(n);
        }
        List<Note> repeatList = new ArrayList<Note>();
        for (int i = 0; i < result.size() - 1; i++) {
            for (int j = result.size() - 1; j > i; j--) {
                if (result.get(j).getNum() == result.get(i).getNum()) {
                    repeatList.add(result.get(j));
                    result.remove(j);
                }
            }
        }
        return result;
    }

    /**
     * 遍历分类
     */

    public List<Note> allLabel(int id) throws SQLException {


        //获得数据库的连接
        Connection conn = DBUtil.getConn();
        StringBuilder sb = new StringBuilder();

        sb.append(" select label,label_num from allnotes ");
        sb.append(" where  id=? ");

        PreparedStatement p = conn.prepareStatement(sb.toString());

        p.setInt(1, id);

        ResultSet rs = p.executeQuery();
        List<Note> result = new ArrayList<>();
        Note n = null;
        while (rs.next()) {
            n = new Note();
            n.setLabel(rs.getString("label"));
            n.setLabelNum(rs.getInt("label_num"));
            result.add(n);
        }
        List<Note> repeatList = new ArrayList<Note>();
        for (int i = 0; i < result.size() - 1; i++) {
            for (int j = result.size() - 1; j > i; j--) {
                if (result.get(j).getLabelNum() == result.get(i).getLabelNum()) {
                    repeatList.add(result.get(j));
                    result.remove(j);
                }
            }
        }
        return result;
    }

    /**
     * 查询分类
     */

    public List<Note> searchByLabel(String label, int id) throws SQLException {


        //获得数据库的连接
        Connection conn = DBUtil.getConn();
        StringBuilder sb = new StringBuilder();

        sb.append(" select headline,content,create_time from allnotes ");
        sb.append(" where  id=? and label=? ");

        PreparedStatement p = conn.prepareStatement(sb.toString());
        p.setInt(1, id);
        p.setString(2, label);

        ResultSet rs = p.executeQuery();
        List<Note> result = new ArrayList<>();
        Note n = null;
        while (rs.next()) {
            n = new Note();
            n.setHeadline(rs.getString("headline"));
            n.setContent(rs.getString("content"));
            n.setCreateTime(rs.getDate("create_time"));
            result.add(n);
        }

        return result;
    }

    /**
     * 点赞和取消点赞
     */

    public void praise(int num, int id) throws SQLException {

        String praiseId = null;

        //获得数据库的连接
        Connection conn = DBUtil.getConn();
        StringBuilder sb = new StringBuilder();

        //判断是否点过赞
        sb.append(" select praise_id from allnotes ");
        sb.append(" where  num=? ");

        PreparedStatement p = conn.prepareStatement(sb.toString());
        p.setInt(1, num);

        ResultSet rs = p.executeQuery();
        while (rs.next()) {
            praiseId = rs.getString("praise_id");
        }
        //将点赞人的id变为String类型
        String ss = String.valueOf(id);
        String s = ss + ",";
        //开始判断
        if (praiseId.indexOf(ss) != -1) {

            //包含 则取消赞（删除praiseId）

            String sql = " update allnotes " +
                    " set praise_points=?,praise_id=?" +
                    " where num=? ";

            //执行sql语句方法

            PreparedStatement p1 = conn.prepareStatement(sql);
            int i = queryPoints(num) - 1;
            String s1 = praiseId.replace(s, "");
            p1.setInt(1, i);
            p1.setString(2, s1);
            p1.setInt(3, num);

            //调用execute时上述语句才应用
            p1.execute();

        } else {
            //不包含 则点赞

            String sql = " update allnotes " +
                    " set praise_points=?,praise_id=?" +
                    " where num=? ";
            //执行sql语句方法
            PreparedStatement p2 = conn.prepareStatement(sql);

            int i = queryPoints(num) + 1;
            String s1 = praiseId + id + ",";
            p2.setInt(1, i);
            p2.setString(2, s1);
            p2.setInt(3, num);

            //调用execute时上述语句才应用
            p2.execute();

        }
    }

    /**
     * 查询点赞数
     */

    public int queryPoints(int num) throws SQLException {
        int result = 0;
        //获得数据库的连接
        Connection conn = DBUtil.getConn();
        StringBuilder sb = new StringBuilder();

        //先找id
        sb.append(" select praise_points from allnotes ");
        sb.append(" where  num=? ");

        PreparedStatement p = conn.prepareStatement(sb.toString());
        p.setInt(1, num);

        ResultSet rs = p.executeQuery();

        while (rs.next()) {
            result = rs.getInt("praise_points");
        }
        return result;
    }

}


